﻿CREATE TABLE Teacher( Id INT  IDENTITY NOT NULL PRIMARY KEY, [Name] NVARCHAR(25), Age INT, Gender BIT )
DROP TABLE IF EXISTS Teacher

 

CREATE     CLUSTERED  INDEX IX_Teacher_Name ON Teacher([Name])

-- [type]：1 聚集; >1 非聚集
SELECT [name], [type], is_unique, is_primary_key, is_unique_constraint 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('Teacher')





  SELECT*
 -- Age,AVG(Score)
  FROM Student

  GROUP BY Age
  HAVING AVG(Score)>9

  SELECT * FROM Student
  GROUP BY Age



  SELECT *FROM Teacher
  --WHERE [Name]=N'飞哥'
  WHERE Gender=1
  CREATE INDEX IX_Teacher_Gender ON Teacher(Gender)


  INSERT  Teacher VALUES(N'飞哥',40,1)
  





ALTER TABLE  Teacher
DROP COLUMN Id

DROP INDEX Teacher.IX_Teacher_Id


INSERT Teacher
SELECT [Name],Age,Gender FROM Teacher
   









CREATE TABLE Teacher( Id INT, [Name] NVARCHAR(25), Age INT, Gender BIT )
CREATE  CLUSTERED INDEX IX_Teacher_Id ON Teacher(Id)
DROP TABLE Teacher



ALTER TABLE Teacher 
--ALTER COLUMN  Id INT NOT NULL



ADD CONSTRAINT  PK_Teacher_Id PRIMARY KEY(Id)



SELECT *FROM Student
SELECT Age,AVG(Score)  
FROM Student
GROUP BY Age



SELECT Age,AVG(Score)  
FROM Student
GROUP BY Age
HAVING AVG(Score)>90

SELECT *FROM Student
SELECT *FROM Teacher
DELETE Teacher
WHERE Id>2

UPDATE Teacher SET [Name]=N'小鱼',  Age =25WHERE Id=2
ALTER TABLE Student
ADD TeacherId INT CONSTRAINT FK_   FOREIGN KEY REFERENCES Teacher(Id)
UPDATE Student SET TeacherId=1 WHERE Id=1

 ALTER TABLE Student -- 修改表 
ADD CONSTRAINT FK_Teacher_Id -- 添加约束，指定约束名称，外键建议以FK开头，后跟表和列名 
FOREIGN KEY (TeacherId) -- 约束类型：外键，且外键列为TeacherId 
REFERENCES Teacher(Id) -- 外键表为Teacher，作为外键使用的列为Teacher上的Id
CREATE TABLE Major
( 
Id INT NOT NULL PRIMARY KEY, 
[Name] NVARCHAR(25), 
TaughtBy INT/* CONSTRAINT FK_Teacher_Id*/ FOREIGN KEY REFERENCES Teacher(Id) 
)

SELECT *FROM TSCORE
ALTER TABLE TSCORE
--ALTER TABLE TScore ADD StudentId INT FOREIGN  KEY REFERENCES Student(Id)
--DROP CONSTRAINT FK__TSCORE__StudentI__66EA454A;
ADD CONSTRAINT  FK_TSCORE_StudentId FOREIGN KEY(StudentId) REFERENCES Student(Id)
ON DELETE CASCADE

CREATE TABLE TSCORE
( 
[Name] NVARCHAR(20), 
[Subject]   NVARCHAR(20), 
Score INT,
)
SELECT *FROM Student
SELECT * FROM Teacher
BEGIN TRAN 
--DELETE Student WHERE Id=1
DELETE Teacher WHERE Id=1
ROLLBACK
UPDATE Student SET TeacherId=5
WHERE Id=3


CREATE TABLE Student2Teacher
(
StudentId INT CONSTRAINT FK_S2T_StudentId FOREIGN KEY REFERENCES Student(Id),
TeacherId INT CONSTRAINT FK_S2T_TeacherId FOREIGN KEY REFERENCES Student(Id)
)
INSERT Student2Teacher VALUES(1,1)

SELECT * FROM Student s -- *指的是Join过后两张表的所有列，s是给表Student的别名 
JOIN City c -- 给表City一个别名，使用JOIN将Student和City连接起来 
ON s.FromCityId = c.Id -- 指定表Student（别名s）和City（别名c）连接的条件
CREATE TABLE City
(

Id INT ,
[Name] NVARCHAR(20),
Province NVARCHAR(20),

 
)
ALTER TABLE Student
ADD FromCityId INT

INSERT City VALUES(1,N'遵义',N'贵州')
INSERT City VALUES(1,N'郑州',N'河南')
INSERT City VALUES(1,N'成都',N'四川')
 
 UPDATE Student SET FromCityId=3 WHERE Id=1
 UPDATE Student SET FromCityId=2 WHERE Id=3
 UPDATE Student SET FromCityId=1 WHERE Id=100
 UPDATE Student SET FromCityId=4 WHERE Id=105


 SELECT 
s.[Name], -- Student表的Name 
c.Province AS Province, -- City表的FromProvince，还给一个列别名Province 
c.[Name] City -- City表的FromCity，还给一个列别名City 
FROM Student s JOIN City c 
ON s.FromCityId = c.Id 


SELECT*FROM Student2Teacher
SELECT*FROM Student





SELECT [Name], N'总体' ,Score FROM Student -- 在Student中补充了一列以对其 
UNION -- 没有ALL，会清除重复行 
SELECT [UserName], [Subject], Score FROM Exam 
ORDER BY [Name] -- 列名以第一个查询子句为准
 --ORDER BY [UserName]                -- 非第一个查询子句的列名会报错





 SELECT [Name] ,Score- (SELECT AVG(Score)FROM Student)FROM Student
 SELECT *FROM Student
 UPDATE Student SET Score=(SELECT Score FROM Student WHERE ID=3)
 WHERE ID=100

 SELECT MAX(ID) FROM Student GROUP  BY Score
 SELECT Score FROM Student
 DELETE Student WHERE ID NOT IN (SELECT MAX(ID) FROM Student GROUP  BY Score)
 SELECT * FROM  TSCORE
SELECT MAX(Score)FROM TSCORE GROUP BY [Name]

 INSERT TSCORE VALUES(N'飞哥',N'SQL',98)
 INSERT TSCORE VALUES(N'飞哥',N'C#',89)
 INSERT TSCORE VALUES(N'飞哥',N'JAVAScript',76)
 INSERT TSCORE VALUES(N'ls',N'C#',87)
 INSERT TSCORE VALUES(N'ls',N'SQL',95)
 INSERT TSCORE VALUES(N'ls',N'JAVAScript',88)
 IF (200>ALL(SELECT Id FROM Student ))SELECT 1


 SELECT * FROM TSCORE  ot 
 WHERE Score=
 (SELECT  MAX(Score) FROM TSCORE it
 WHERE it.[Name]=ot.[Name]
 GROUP BY [Name])


 SELECT *,AVG(Score) FROM TSCORE ot
 WHERE Score >=
 (
  SELECT  AVG (Score) FROM TSCORE it
  WHERE ot.[Name]=it.[Name]
 )

SELECT * FROM( 
	SELECT Id, 
	Id*2 AS DoubleId   -- 这是一个计算列，注意一定要加上一个别名 
	FROM Student) ns   -- 这个ns的别名也是一样必须要有的，即使没有后面的WHERE子句 
WHERE ns.DoubleId > 10 -- WHERE 子句使用子查询表别名ns
GO
WITH ns AS(
SELECT Id, 
	Id*2 AS DoubleId   -- 这是一个计算列，注意一定要加上一个别名 
	FROM Student)
	SELECT * FROM ns
	WHERE ns.DoubleId > 10
	

WITH MonthedStudent				-- 第一个CET：MonthedStudent 
AS(SELECT * FROM Student),  -- 从Student表获取数据 
BestStudent					-- 第二个CET：BestStudent，注意不再需要WITH 
AS(SELECT * FROM TSCORE)		-- 还可以从Exam表中获取数据 

SELECT * FROM MonthedStudent JOIN BestStudent	
ON MonthedStudent.[Name]=BestStudent.[Name]

SELECT MONTH('2019/3/4')

WITH MonthedStudent AS( 
	SELECT *, Month(Enroll) AS EnrollMonth FROM Student 
) 
SELECT * FROM MonthedStudent oms				--第一次使用MonthedStudent 
WHERE Score = ( 
	SELECT MAX(Score) FROM MonthedStudent ims	--第二次使用MonthedStudent 
	WHERE oms.[EnrollMonth] = ims.[EnrollMonth] 
)


SELECT * FROM( 
	SELECT Id, 
	Id*2 AS DoubleId   -- 这是一个计算列，注意一定要加上一个别名 
	FROM Student) ns   -- 这个ns的别名也是一样必须要有的，即使没有后面的WHERE子句 
WHERE ns.DoubleId > 10 -- WHERE 子句使用子查询表别名ns

SELECT * FROM Student  ORDER BY ID
SELECT TOP 2 * FROM  Student ORDER BY ID

INSERT Student VALUES(4,N'ls' ,'2021-2-3',23,1,2,2 )
INSERT Student VALUES(5,N'HYL','2021-2-3',23,1,2,2 )
SELECT TOP 3 * FROM Student
WHERE ID NOT IN (SELECT TOP 3 ID FROM Student ORDER BY ID
)
ORDER BY ID

SELECT * FROM Student WHERE Id NOT IN
(SELECT TOP 2 Id FROM Student ORDER By Id )


SELECT * FROM sys.indexes
GO
--CREATE VIEW V_Student -- V_Student是新创建View的名称 
CREATE VIEW V_Student([No],[Name],[Year],[Month],[Day],Age,Score)
AS SELECT Id, [Name], 
YEAR(Enroll) YearEnroll, -- 新增的计算列 
Month(Enroll) MonthEnroll, -- 必须指定列名 
Day(Enroll) DayEnroll, -- 列名不能重复 
Age, Score FROM Student -- 还可以添加其他的WHERE/GROUP/HAVING等子句
GO 
SELECT * FROM V_Student
DROP VIEW V_Student
GO
ALTER VIEW V_Student ([Id],[Name],[Year],[Month],[Day],Age,Score)
AS 
SELECT Id, [Name], 
YEAR(Enroll) YearEnroll, -- 新增的计算列 
Month(Enroll) MonthEnroll, -- 必须指定列名 
Day(Enroll) DayEnroll, -- 列名不能重复 
Age, Score FROM Student -- 还可以添加其他
GO

UPDATE V_Student SET Score = 0 WHERE Id = 1 -- 修改视图V_Student 
SELECT * FROM Student -- 基表Student数据发生了改变 
DELETE V_Student WHERE Id = 1 -- 修改基表Student 
SELECT * FROM V_Student -- 视图的查询结果发生了改变
UPDATE V_Student SET YearEnroll = 2020 -- YearEnroll是通过YEAR(Enroll)获得的
ALTER TABLE Student DROP COLUMN Enroll
GO
CREATE VIEW V_Student_Schema 
WITH SCHEMABINDING 
AS 
SELECT Id, [Name],  Score
FROM dbo.Student 
GO
ALTER VIEW V_Student_Schema 
WITH SCHEMABINDING 
AS SELECT Id, [Name],  Score, Age 
FROM dbo.Student WHERE Score > 60 -- 添加这么一个WHERE子句
GO 
INSERT V_Student_Schema([Name],  Score, Age) 
VALUES(N'Where?',  58, 23) -- 注意Score是58 （<=60）
SELECT * FROM  V_Student_Schema
SELECT * FROM Student
GO
ALTER VIEW V_Student_Schema 
WITH SCHEMABINDING 
AS SELECT Id, [Name],  Score, Age 
FROM dbo.Student 
WHERE Score > 60 WITH CHECK OPTION -- 添加到WHERE子句之后
GO
UPDATE V_Student_Schema  SET Score=NULL
GO
CREATE VIEW V_Student_Encrypt 
WITH ENCRYPTION 
AS SELECT * FROM Student
GO
SELECT * FROM  City
SELECT * FROM Student
GO
CREATE VIEW VStudentAndCity
AS
SELECT s.[Name] sName,Age,Score,c.[Name] cName ,c.[Province]
FROM Student s JOIN City c 
ON s.FromCityId=c.Id
GO
SELECT * FROM VStudentAndCity
UPDATE VStudentAndCity SET cName = N'贵阳' 
WHERE sName = N'fegei'
GO

ALTER TABLE City  ALTER COLUMN Id  BIGINT

SELECT * FROM City
GO 
CREATE VIEW V_City 
WITH SCHEMABINDING
AS
SELECT Id,[Name] ,Province
FROM dbo.City
GO
CREATE UNIQUE CLUSTERED INDEX IX_City_Id
ON V_City(Id)

CREATE TABLE Major
( 
Id INT NOT NULL PRIMARY KEY, 
[Name] NVARCHAR(25), 
TaughtBy INT CONSTRAINT FK_Teacher_Id FOREIGN KEY(TeacherId) REFERENCES Teacher(Id) 
)

CREATE  TABLE #Ordeaed
(
Id INT IDENTITY PRIMARY KEY,
[Name]  NVARCHAR(10) NOT NULL,
Age SMALLINT,
[Description] NVARCHAR(500)
)
SELECT * FROM #Ordeaed
DECLARE @TVariable TABLE(Id INT,Gender BIT) -- 声明
INSERT @TVariable VALUES(986,1)    -- 像表一样插入数据
SELECT * FROM @TVariable         -- 像表一样查询





SELECT * FROM Student ORDER BY Score



CREATE TABLE ##tmp_Student( tId INT , [sId] INT);
INSERT ##tmp_Student(tId) VALUES(1)
INSERT ##tmp_Student(tId) VALUES(119)
INSERT ##tmp_Student(tId) VALUES(100)
INSERT ##tmp_Student(tId) VALUES(128)
INSERT ##tmp_Student(tId) VALUES(131)
INSERT ##tmp_Student(tId) VALUES(129)
INSERT ##tmp_Student(tId) VALUES(127)
INSERT ##tmp_Student(tId) VALUES(130)
INSERT ##tmp_Student(tId) VALUES(105)

INSERT ##tmp_Student
SELECT Id FROM Student ORDER BY Score
SELECT * FROM ##tmp_Student

SELECT * FROM Student s
JOIN ##tmp_Student ts ON s.Id=ts.tId
WHERE tId BETWEEN 1 AND 3


SELECT * FROM ##tmp_Student

CREATE TABLE BangMoney(
         [Name]  NVARCHAR(25),
		 Balance INT
		 )
		 INSERT BangMoney VALUES(N'陈元',100)
		  INSERT BangMoney VALUES(N'辛龙泰',100)



BEGIN TRAN
 UPDATE BangMoney SET Balance+=5
 BEGIN TRY
 UPDATE BangMoney SET Balance-=100 WHERE [Name]=N'辛龙泰'
 UPDATE BangMoney SET Balance+=100 WHERE [Name]=N'陈元'
  COMMIT
   SELECT * FROM BangMoney
  END TRY 
  BEGIN CATCH
  ROLLBACK
  END CATCH





  BEGIN TRY
  UPDATE BangMoney SET Balance+=5
 BEGIN TRAN
 UPDATE BangMoney SET Balance-=100 WHERE [Name]=N'辛龙泰'
 UPDATE BangMoney SET Balance+=100 WHERE [Name]=N'陈元'
  COMMIT
   SELECT * FROM BangMoney
  END TRY 
  BEGIN CATCH
  ROLLBACK
  END CATCH




  
 ALTER TABLE BangMoney
 ADD CONSTRAINT CK_BangMoney_Balance CHECK(Balance>=0)

 SELECT * FROM BangMoney


 BEGIN TRY
  BEGIN TRAN
   UPDATE BangMoney SET Balance+=5
		  BEGIN TRAN;
		  SAVE TRAN inner_tran
		  BEGIN TRY
			 UPDATE BangMoney SET Balance-=200 WHERE [Name]=N'辛龙泰'
			 UPDATE BangMoney SET Balance+=200WHERE [Name]=N'陈元'
		  COMMIT
	  END TRY 
	  BEGIN CATCH

	  ROLLBACK TRAN inner_tran;
	  --COMMIT TRAN;
	  END CATCH
COMMIT


  END TRY
  BEGIN CATCH
   ROLLBACK
  END CATCH
SELECT  @@TRANCOUNT





SET IMPLICIT_TRANSACTIONS ON

 ---------------------------------
 UPDATE BangMoney SET Balance=100;
--等同于
 BEGIN TRAN
UPDATE BangMoney SET Balance=100;
COMMIT;
-------------------------------------
--等同于
 BEGIN TRAN
UPDATE BangMoney SET Balance=100;

-------------------------------------



  SELECT * FROM BangMoney
  SET IMPLICIT_TRANSACTIONS OFF



BEGIN TRAN
UPDATE BangMoney SET Balance=97 WHERE [Name]=N'陈元'
SELECT * FROM BangMoney
SELECT @@TRANCOUNT
ROLLBACK

COMMIT
SELECT * FROM BangMoney
SELECT @@TRANCOUNT






BEGIN TRANSACTION
SELECT * FROM BangMoney WHERE [Name]=N'辛龙泰'; --第一次查找
SELECT * FROM BangMoney WHERE [Name]=N'辛龙泰'; --第二次查找
SELECT @@TRANCOUNT
COMMIT
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT * FROM BangMoney
 
BEGIN TRANSACTION--
UPDATE BangMoney SET Balance=10

--DECLARE @name NVARCHAR(20) = N'feige'
DECLARE @name NVARCHAR(20)= N'feige' --,@age INT  
--SET @name = N'feige'
PRINT @name

INSERT Student([Name]) VALUES('2019/2/3')
SELECT @@IDENTITY
SELECT * FROM Student

DECLARE @name NVARCHAR(20)   -- = N'feige' 
IF(@name IS NOT NULL) 
--BEGIN
   PRINT '@name IS NULL'
   PRINT N'哦豁'
   --END
--ELSE
   --PRINT @name
   SET IDENTITY_INSERT KeyWord ON;
    SET IDENTITY_INSERT KeyWord OFF;

ALTER TABLE KeyWord
ADD  Id2 NVARCHAR(50)

INSERT KeyWord(KWord) VALUES(N'SQL')
INSERT KeyWord(KWord) VALUES(N'JAVA')
INSERT KeyWord(KWord) VALUES(N'HTML')

SELECT * FROM KeyWord

UPDATE KeyWord SET Id2 = NULL;


ALTER TABLE KeyWord
DROP COLUMN ID 



DECLARE @n INT = 0
WHILE (@n <50)
 BEGIN 
   SET @n+=1

PRINT FLOOR(RAND()*1000)
END


PRINT FLOOR(RAND()*1000)
	IF '    A'='a' SELECT 1
	PRINT RIGHT(LEFT (N'源栈欢迎您',4),2)
	DECLARE @source NVARCHAR(20)=N'源栈欢迎您'
	SET @source=LEFT(@source,2)
	SET @source=RIGHT(@source,1)
	PRINT @source
	SELECT LEFT([Name],2) FROM  Student
	PRINT SUBSTRING(N'源栈欢迎您'+'!',4,3)
	PRINT CHARINDEX(N'栈',N'源栈欢迎您'+'!')
	PRINT REPLACE(N'源栈欢迎您'+'!',N'您',N'英俊的您')
		PRINT SPACE(10)+'*'
		SELECT DATEDIFF(HOUR,'2019/2/3',GETDATE())
		SELECT * FROM Student
ALTER TABLE Student
ADD Enroll DATETIME
INSERT Student(Name,IsFemale,Enroll)VALUES(N'lWW',1,'源栈欢迎您')
PRINT 2022/3/2

	DECLARE @Age INT
	SET @Age='23'
	PRINT @Age+1
	PRINT 'MY'+CONVERT(NVARCHAR(10),@Age)
	PRINT CONVERT(INT,27.83)-20
	PRINT CAST(27.83 AS INT )-20
	PRINT 27.83-12
	PRINT '2020/2/2'
	SELECT CONVERT(TIME,'2020/2/2')
	GO
CREATE FUNCTION YZDiscount(@Price INT,@Rate FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN @Price * @Rate
END   
go
PRINT dbo.YZDiscount(888,1.2)
GO
CREATE FUNCTION  YzInIineTABLE(@number INT)
RETURNS TABLE
RETURN SELECT TOP (@number) * FROM Student
GO
SELECT * FROM  dbo.YzInIineTABLE(3)
GO
CREATE FUNCTION YzTable(@id INT,@name NVARCHAR(20))
RETURNS @t TABLE (Id INT,[Name] NVARCHAR(20))
AS
BEGIN
 INSERT @t VALUES(@id,@name)
 RETURN
 END
 GO
 SELECT * FROM YzTable(3,N'FG')



GO	
CREATE PROCEDURE GetExcellentStudents
@score INT = 80,
@count INT OUTPUT
AS
SET NOCOUNT ON;
UPDATE Student SET Score+=5
SELECT * FROM Student WHERE [Score] >@score;
SET @count= (SELECT COUNT(*) FROM Student WHERE[Score]>@score)
SET NOCOUNT OFF;
DECLARE @count INT
EXECUTE @count =GetExcellentStudents DEFAULT--,@count OUTPUT
SELECT @count
SELECT * FROM Student
UPDATE Student SET Age=100
ALTER TABLE [Student]
ADD CONSTRAINT CK_Age CHECK(Age>0)
INSERT BangMoney(GetBBMoney,Balance,[Name]) VALUES(100,100,N'LS')
INSERT BangMoney(GetBBMoney,Balance,[Name]) VALUES(100,100,N'FG')
SELECT * FROM BangMoney
UPDATE BangMoney SET Balance-=5 WHERE [Name]=N'LS'
UPDATE BangMoney SET Balance+=5 WHERE [Name]=N'FG'